Análise de projetos de infraestrutura com investimento federal no Distrito Federal¶

Teste avaliativo para vaga de bolsista em engenharia/análise de dados (LabLivre). Ver material de referência. Fonte de dados: obrasgov.

Devido às visualizações interativas e limitações do github, é altamente recomendada a visualização deste notebook por meio da página html.

Relatório detalhado de tratamento e análise de dados. Para resumo acessível com visualizações, ver seção 4. Resultados e conclusão.

Observação: IA (Cursor) foi usado para a produção deste relatório, principalmente para agilizar a produção de gráficos.


Sumário:

  1. Extração de dados
  2. Tratamento de dados
    1. Duplicatas
    2. Valores ausentes
    3. Limpeza específica de dados
    4. Tipagem de dados
    5. Colunas aninhadas
    6. Engenharia de características
    7. Carregamento final dos dados
  3. Análise de dados
    1. Informações básicas
    2. Qualidade dos dados
    3. Análise categórica
    4. Análise de investimento
    5. Análise temporal
    6. Dados geográficos (GIS)
    7. Processamento de linguagem natural (NLP)
  4. Resultados e conclusão

In [198]:
import json
import time
import requests
import glob
import sqlite3
import datetime

# dados e visualização
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import altair as alt

# estatística
import pingouin as pg
import statsmodels.api as sm

# machine learning e NLP
import hdbscan
import umap.umap_ as umap

# mapa
import folium

# cores do lablivre
palette = ["#412355", "#F2701C", "#18CEE6"]

1. Extração de dados¶

In [199]:
# o script de coleta de dados foi executado separadamente


def get_data(page: int) -> dict:
    url = "https://api.obrasgov.gestao.gov.br/obrasgov/api/projeto-investimento"
    params = {"uf": "DF", "pagina": page, "tamanhoDaPagina": 100}
    headers = {"accept": "*/*"}

    response = requests.get(url, params=params, headers=headers)

    if response.status_code != 200:
        raise Exception(f"Failed to get data: {response.status_code}")

    return response.json()


def main():
    # deselegante mas resolve imediatamente
    for page in range(100):
        response = get_data(page)
        print(f"Page {page} processed")

        # salvar dados brutos para não depender da api
        with open(f"data/data-{page}.json", "w", encoding="utf-8") as f:
            json.dump(response["content"], f, indent=4)
        print(f"Data saved to data/data-{page}.json")

        time.sleep(1)

        page += 1


# if __name__ == "__main__":
#     main()
In [200]:
def load_json_files() -> list[dict]:
    json_files = glob.glob("data/data-*.json")
    all_records = []

    for file in json_files:
        with open(file, "r", encoding="utf-8") as f:
            records = json.load(f)
            all_records.extend(records)

    return all_records

Dados iniciais:

In [201]:
df = pd.DataFrame(load_json_files())
df.info(memory_usage="deep")
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 834 entries, 0 to 833
Data columns (total 31 columns):
 #   Column                              Non-Null Count  Dtype 
---  ------                              --------------  ----- 
 0   idUnico                             834 non-null    object
 1   nome                                834 non-null    object
 2   cep                                 400 non-null    object
 3   endereco                            430 non-null    object
 4   descricao                           834 non-null    object
 5   funcaoSocial                        834 non-null    object
 6   metaGlobal                          834 non-null    object
 7   dataInicialPrevista                 832 non-null    object
 8   dataFinalPrevista                   832 non-null    object
 9   dataInicialEfetiva                  23 non-null     object
 10  dataFinalEfetiva                    7 non-null      object
 11  dataCadastro                        834 non-null    object
 12  especie                             830 non-null    object
 13  natureza                            834 non-null    object
 14  naturezaOutras                      211 non-null    object
 15  situacao                            834 non-null    object
 16  descPlanoNacionalPoliticaVinculado  287 non-null    object
 17  uf                                  834 non-null    object
 18  qdtEmpregosGerados                  164 non-null    object
 19  descPopulacaoBeneficiada            174 non-null    object
 20  populacaoBeneficiada                169 non-null    object
 21  observacoesPertinentes              129 non-null    object
 22  isModeladaPorBim                    591 non-null    object
 23  dataSituacao                        834 non-null    object
 24  tomadores                           834 non-null    object
 25  executores                          834 non-null    object
 26  repassadores                        834 non-null    object
 27  eixos                               834 non-null    object
 28  tipos                               834 non-null    object
 29  subTipos                            834 non-null    object
 30  fontesDeRecurso                     834 non-null    object
dtypes: object(31)
memory usage: 2.3 MB
In [202]:
pd.set_option("display.max_columns", None)
df.head()
Out[202]:
idUnico nome cep endereco descricao funcaoSocial metaGlobal dataInicialPrevista dataFinalPrevista dataInicialEfetiva dataFinalEfetiva dataCadastro especie natureza naturezaOutras situacao descPlanoNacionalPoliticaVinculado uf qdtEmpregosGerados descPopulacaoBeneficiada populacaoBeneficiada observacoesPertinentes isModeladaPorBim dataSituacao tomadores executores repassadores eixos tipos subTipos fontesDeRecurso
0 1828.53-16 SISEG - Sistema integrado de segurança 1 implantação dos sistemas de monitoramento por ... implantação dos sistemas de monitoramento por ... Modernização do sistema de segurança do Banco ... 2019-02-04 2024-02-06 None None 2021-05-12 Recuperação Projeto Cadastrada None DF None None None None None 2021-05-12 [] [{'nome': 'BANCO CENTRAL DO BRASIL', 'codigo':... [] [{'id': 1, 'descricao': 'Administrativo'}] [{'id': 5, 'descricao': 'Administrativo', 'idE... [{'id': 59, 'descricao': 'Obras em Imóveis de ... [{'origem': 'Federal', 'valorInvestimentoPrevi...
1 1711.53-18 Reforma do espelho d'água do Edifício-Sede do ... 1 Reforma do espelho d'água do Edifício-Sede do ... Reestabelecer a impermeabilização do espelho d... Recuperação do sistema de impermeabilização e ... 2021-01-04 2021-05-18 None None 2021-05-04 Reforma Obra Cadastrada None DF None None None None None 2021-05-04 [{'nome': 'BANCO CENTRAL DO BRASIL', 'codigo':... [{'nome': 'BANCO CENTRAL DO BRASIL', 'codigo':... [] [{'id': 1, 'descricao': 'Administrativo'}] [{'id': 5, 'descricao': 'Administrativo', 'idE... [{'id': 59, 'descricao': 'Obras em Imóveis de ... [{'origem': 'Federal', 'valorInvestimentoPrevi...
2 1989.53-01 Elaboração de projetos arquitetônicos e de eng... 1 Campus Universitário Darcy Ribeiro, Brasília - DF Contratação de empresa especializada para elab... Obras para unidades acadêmicas de ensino e pes... Elaboração de projetos de engenharia e Arquite... 2021-05-18 2021-09-29 None None 2021-05-17 Construção Projeto None Cadastrada None DF None None None None None 2021-05-17 [] [{'nome': 'FUNDACAO UNIVERSIDADE DE BRASILIA',... [{'nome': 'FUNDACAO UNIVERSIDADE DE BRASILIA',... [{'id': 1, 'descricao': 'Administrativo'}] [{'id': 8, 'descricao': 'Educação', 'idEixo': 1}] [{'id': 46, 'descricao': 'Instituições Federai... [{'origem': 'Federal', 'valorInvestimentoPrevi...
3 2004.53-58 SISEG - Sistema integrado de segurança 1 implantação dos sistemas de monitoramento por ... implantação dos sistemas de monitoramento por ... Modernização do sistema de segurança do Banco ... 2019-02-04 2024-02-06 None None 2021-05-18 Recuperação Projeto Cadastrada None DF None None None None None 2021-05-18 [{'nome': 'BANCO CENTRAL DO BRASIL-ORC.FISCAL/... [{'nome': 'BANCO CENTRAL DO BRASIL', 'codigo':... [] [{'id': 1, 'descricao': 'Administrativo'}] [{'id': 5, 'descricao': 'Administrativo', 'idE... [{'id': 59, 'descricao': 'Obras em Imóveis de ... [{'origem': 'Federal', 'valorInvestimentoPrevi...
4 2617.53-02 CENTRO DE FORMAÇÃO TECNOLOGICA - CFT - CAMPUS ... 1 Área Especial n° 01, Quadra 16, Cidade do Auto... construção um galpão com pé direito alto, que ... atender a comunidade acadêmica do Campus Estru... aumentar a oferta de cursos técnicos/tecnológi... 2020-12-01 2021-10-27 None None 2021-06-18 Construção Obra Cadastrada triplicar as matrículas da educação profission... DF None None None None None 2021-06-18 [{'nome': 'INSTITUTO FED. ED. CIENCIA E TEC. D... [{'nome': 'INSTITUTO FED. ED. CIENCIA E TEC. D... [{'nome': 'MINISTÉRIO DA EDUCAÇÃO', 'codigo': ... [{'id': 4, 'descricao': 'Social'}] [{'id': 46, 'descricao': 'Educação', 'idEixo':... [{'id': 84, 'descricao': 'Educação', 'idTipo':... [{'origem': 'Federal', 'valorInvestimentoPrevi...

2. Tratamento de dados¶

In [203]:
# colunas relativa a dados aninhados, represetando dados com relação many to many

nested_cols = [
    "tomadores",
    "executores",
    "repassadores",
    "eixos",
    "tipos",
    "subTipos",
    "fontesDeRecurso",
]

2.1 Duplicatas¶

In [ ]:
from analyse_duplicates import check_duplicates, analyze_false_duplicates

# existem duplicatas que só diferem no conteúdo das listas.
# não quero te entediar com esse código gerado por IA então movi para outro arquivo
# Para mais detalhes, consultar o arquivo `apendices/duplicate_records_report.txt`.

check_duplicates(df)
analyze_false_duplicates(df, "apendices/duplicate_records_report")
Columns with lists: ['tomadores', 'executores', 'repassadores', 'eixos', 'tipos', 'subTipos', 'fontesDeRecurso']
Number of duplicate rows (not considering list columns): 122
Number of duplicate rows: 87
Number of 'false duplicate' rows: 70
Unique idUnico values in false duplicates: 35
Report written to duplicate_records_report.txt

Após investigação detalhada, todas as 'falsas duplicatas' tem o seguinte formato:

133: { id: 90, descricao: "Preservação do Patrimônio", idTipo: 5 }
491: { id: 90, descricao: "Preservação do Patrimônio", idTipo: 43 }

Isso mostra que são duplicatas -- portanto serão removidas.

In [205]:
df.duplicated(subset=["idUnico"]).sum()
df.drop_duplicates(subset=["idUnico"], inplace=True)

2.2 Valores ausentes¶

In [206]:
# checar apenas colunas que não são aninhadas
object_cols = df.drop(columns=nested_cols, axis=1)

info_df = pd.DataFrame(
    {
        "NaN Values": object_cols.isna().sum(),
        "% NaN": (object_cols.isna().sum() / len(object_cols) * 100).round(1),
    }
)
info_df[info_df["% NaN"] > 1].sort_values(by="% NaN", ascending=False)
Out[206]:
NaN Values % NaN
dataFinalEfetiva 707 99.3
dataInicialEfetiva 690 96.9
observacoesPertinentes 606 85.1
qdtEmpregosGerados 574 80.6
populacaoBeneficiada 572 80.3
descPopulacaoBeneficiada 568 79.8
naturezaOutras 522 73.3
descPlanoNacionalPoliticaVinculado 464 65.2
cep 365 51.3
endereco 337 47.3
isModeladaPorBim 216 30.3
In [ ]:
hoje = datetime.datetime.now()
df["dataFinalPrevista"] = pd.to_datetime(df["dataFinalPrevista"])

# Calcular quantos projetos estão atrasados
atrasados = df[df["dataFinalPrevista"] < hoje]
print(
    f"{len(atrasados)} projetos ({(len(atrasados)/len(df)*100):.1f}%) estão com data final prevista no passado"
)
515 projetos (72.3%) estão com data final prevista no passado

Há muitos valores ausentes que provavelmente não são erros (como em campos do tipo 'outras observações'). Mais notável é a ausência de qdtEmpregosGerados, populacaoBeneficiada, cep e endereco.

Considerando que 75% dos projetos possuem data final prevista no passado, a ausência de datas indica que estes dados não foram atualizados.

2.3 Limpeza específica de dados¶

Encoding¶

In [208]:
# erro de encoding. Texto em UTF-8, foi lido como Latin-1 (ISO-8859-1)
# toddos tem uma rodovia como endereço, indicando que o erro é relacionado à fonte dos dados.

enconding_issue = df[df["descricao"].str.contains("çÃ")]
print(f"Pelo menos {enconding_issue.shape[0]} projetos com erro de encoding")
print("exemplo:", df[df["descricao"].str.contains("çÃ")].head(1)["descricao"])
Pelo menos 18 projetos com erro de encoding
exemplo: 63    prestação dos serviços de gerenciamento dos...
Name: descricao, dtype: object
In [209]:
def fix_encoding(item: any) -> any:
    if isinstance(item, str):
        try:
            return item.encode("latin-1").decode("utf-8")
        except:
            return item
    return item


for col in df.select_dtypes(include=["object"]).columns:
    df[col] = df[col].apply(fix_encoding)

Registros incorretos¶

In [210]:
# há um sujeito chamado Ronald Alves Vieira e ele está testando em produção
test_df = df[df["nome"].str.contains("Ronald", case=False, na=False)]
test_df.head()
Out[210]:
idUnico nome cep endereco descricao funcaoSocial metaGlobal dataInicialPrevista dataFinalPrevista dataInicialEfetiva dataFinalEfetiva dataCadastro especie natureza naturezaOutras situacao descPlanoNacionalPoliticaVinculado uf qdtEmpregosGerados descPopulacaoBeneficiada populacaoBeneficiada observacoesPertinentes isModeladaPorBim dataSituacao tomadores executores repassadores eixos tipos subTipos fontesDeRecurso
79 4543.53-79 202111-22-Ronald 1 70067-901 2021122-Ronald - Endereço Completo 2021122-Ronald - Descrição do Projeto 2021122-Ronald - Descrição Funç]ap Social 20211122-Ronald - Descrição Meta Global 2021-12-10 2021-12-10 None None 2021-11-17 Fabricação Projeto outros Cadastrada None DF None None None None None 2021-11-17 [{'nome': 'FUND.UNIVERSIDADE FEDERAL VALE SAO ... [{'nome': 'MINISTÉRIO DA INTEGRAÇÃO E DO DESEN... [{'nome': 'MINISTÉRIO DA INTEGRAÇÃO E DO DESEN... [{'id': 4, 'descricao': 'Social'}] [{'id': 45, 'descricao': 'Desenvolvimento', 'i... [{'id': 38, 'descricao': 'Saneamento', 'idTipo... [{'origem': 'Federal', 'valorInvestimentoPrevi...
80 4540.53-87 202111-22-Ronald 1 70067-901 2021122-Ronald - Endereço Completo 2021122-Ronald - Descrição do Projeto 2021122-Ronald - Descrição Funç]ap Social 20211122-Ronald - Descrição Meta Global 2021-12-10 2021-12-10 None None 2021-11-17 Reforma Obra outros Cadastrada None DF None None None None None 2021-11-17 [{'nome': 'FUND.UNIVERSIDADE FEDERAL VALE SAO ... [{'nome': 'MINISTÉRIO DA INTEGRAÇÃO E DO DESEN... [{'nome': 'MINISTÉRIO DA INTEGRAÇÃO E DO DESEN... [{'id': 4, 'descricao': 'Social'}] [{'id': 45, 'descricao': 'Desenvolvimento', 'i... [{'id': 38, 'descricao': 'Saneamento', 'idTipo... [{'origem': 'Federal', 'valorInvestimentoPrevi...
82 4542.53-01 202111-22-Ronald 1 70067-901 2021122-Ronald - Endereço Completo 2021122-Ronald - Descrição do Projeto 2021122-Ronald - Descrição Funç]ap Social 20211122-Ronald - Descrição Meta Global 2021-12-10 2021-12-10 None None 2021-11-17 Reforma Projeto de Investimento em Infraestrutura outros Cadastrada None DF None None None None None 2021-11-17 [{'nome': 'FUND.UNIVERSIDADE FEDERAL VALE SAO ... [{'nome': 'MINISTÉRIO DA INTEGRAÇÃO E DO DESEN... [{'nome': 'MINISTÉRIO DA INTEGRAÇÃO E DO DESEN... [{'id': 4, 'descricao': 'Social'}] [{'id': 45, 'descricao': 'Desenvolvimento', 'i... [{'id': 38, 'descricao': 'Saneamento', 'idTipo... [{'origem': 'Federal', 'valorInvestimentoPrevi...
83 4544.53-36 202111-22-Ronald 1 70067-901 2021122-Ronald - Endereço Completo 2021122-Ronald - Descrição do Projeto 2021122-Ronald - Descrição Funç]ap Social 20211122-Ronald - Descrição Meta Global 2021-12-10 2021-12-10 None None 2021-11-17 Reforma Projeto de Investimento em Infraestrutura outros Cadastrada None DF None None None None None 2021-11-17 [{'nome': 'FUND.UNIVERSIDADE FEDERAL VALE SAO ... [{'nome': 'MINISTÉRIO DA INTEGRAÇÃO E DO DESEN... [{'nome': 'MINISTÉRIO DA INTEGRAÇÃO E DO DESEN... [{'id': 4, 'descricao': 'Social'}] [{'id': 45, 'descricao': 'Desenvolvimento', 'i... [{'id': 38, 'descricao': 'Saneamento', 'idTipo... [{'origem': 'Federal', 'valorInvestimentoPrevi...
205 4541.53-44 202111-22-Ronald 1 70067-901 2021122-Ronald - Endereço Completo 2021122-Ronald - Descrição do Projeto 2021122-Ronald - Descrição Funç]ap Social 20211122-Ronald - Descrição Meta Global 2021-12-10 2021-12-10 None None 2021-11-17 Recuperação Projeto de Investimento em Infraestrutura outros Cadastrada None DF None None None None None 2021-11-17 [{'nome': 'FUND.UNIVERSIDADE FEDERAL VALE SAO ... [{'nome': 'MINISTÉRIO DA INTEGRAÇÃO E DO DESEN... [{'nome': 'MINISTÉRIO DA INTEGRAÇÃO E DO DESEN... [{'id': 4, 'descricao': 'Social'}] [{'id': 45, 'descricao': 'Desenvolvimento', 'i... [{'id': 38, 'descricao': 'Saneamento', 'idTipo... [{'origem': 'Federal', 'valorInvestimentoPrevi...
In [211]:
# é possível que hajam mais casos que não encontrei
# é possível que essa limpeza remova dados reais

antes = df.shape[0]

df = df[~df["nome"].str.contains("Ronald", case=False, na=False)]
df = df[~df["nome"].str.contains("Teste", case=False, na=False)]

depois = df.shape[0]

print(
    f"Removidos {antes - depois} projetos por serem registros falsos (efeitos colaterais de testes de integração)"
)
Removidos 22 projetos por serem registros falsos (efeitos colaterais de testes de integração)

Endereços e CEP¶

In [212]:
# remover ceps preenchidos com 1 ou espaço.
df["cep"] = df["cep"].str.replace(r"^1$", "", regex=True)
df["cep"] = df["cep"].str.replace(r"\s+", "", regex=True).replace("", None)

# normalizar ceps
df["cep"] = df["cep"].str.replace("-", "")
df["cep"] = df["cep"].str.replace(".", "")

# remover ceps com menos de 8 dígitos
df["cep"] = df["cep"].apply(lambda x: None if pd.isna(x) or len(str(x)) < 8 else x)

df["cep"].nunique()
Out[212]:
88
In [213]:
# remover enderecos preenchidos com 1 ou espaço.
df["endereco"] = df["endereco"].str.strip()
df["endereco"] = df["endereco"].str.replace(r"^1$", "", regex=True)
df["endereco"] = df["endereco"].str.replace(r"^\s+$", "", regex=True).replace("", None)
df["endereco"].nunique()
Out[213]:
233

2.4 Tipagem¶

In [214]:
df["isModeladaPorBim"] = df["isModeladaPorBim"].astype("boolean")
In [215]:
# variaveis categoricas
df["natureza"] = df["natureza"].astype("category")
df["situacao"] = df["situacao"].astype("category")
df["especie"] = df["especie"].astype("category")
df["uf"] = df["uf"].astype("category")
In [216]:
# variaveis de data
# verifiquei manualmente os registros para erros de formatação mas não encontrei problemas

date_cols = [
    "dataInicialPrevista",
    "dataFinalPrevista",
    "dataInicialEfetiva",
    "dataFinalEfetiva",
    "dataCadastro",
    "dataSituacao",
]

for col in date_cols:
    df[col] = pd.to_datetime(df[col], errors="coerce")
In [217]:
# alguns registros tem strings em vez de ints. esses registros também têm outros problemas; veja abaixo.

# converte para float em vez de int para permitir valores nulos, e transforma strings em nulos
df["qdtEmpregosGerados"] = pd.to_numeric(df["qdtEmpregosGerados"], errors="coerce")
df["populacaoBeneficiada"] = pd.to_numeric(df["populacaoBeneficiada"], errors="coerce")

2.5 Colunas aninhadas¶

Dados via json representam dados aninhados que precisam ser normalizados para armazenamento em um banco de dados relacional.

In [218]:
def normalize_nested_column(
    df: pd.DataFrame, id_col: str, nested_col: str
) -> pd.DataFrame:
    def convert_dict_values_to_str(x):
        # pd.json_normalize(df['tomadores'].explode()) converte o id para float, o que pode causar problemas devido à imprecisão de ponto flutuante mesmo se convertido de volta para int/str. por isso, preciso converter os valores para strings primeiro, o que requer um código um pouco confuso:

        if not isinstance(x, list):
            return x
        return [{key: str(value) for key, value in item.items()} for item in x]

    df[nested_col] = df[nested_col].apply(convert_dict_values_to_str)
    exploded = df[[id_col, nested_col]].explode(nested_col)
    exploded = exploded.dropna(subset=[nested_col])
    normalized = pd.json_normalize(exploded[nested_col])
    normalized[id_col] = exploded[id_col].values
    return normalized


def create_entity_table_from_junction_table(
    old_df: pd.DataFrame, prev_index: str, actual_index: str
) -> pd.DataFrame:
    new_df = old_df.dropna(subset=[prev_index])
    new_df.drop_duplicates(subset=[actual_index], inplace=True)
    new_df.set_index(actual_index, inplace=True)
    new_df.drop(prev_index, axis=1, inplace=True)
    return new_df
In [219]:
# reuni essas tabelas em uma só, pois são todas referentes a instituicoes e usam o mesmo sistema de códigos.

projeto_tomadores_df = normalize_nested_column(df, "idUnico", "tomadores")
projeto_executores_df = normalize_nested_column(df, "idUnico", "executores")
projeto_repassadores_df = normalize_nested_column(df, "idUnico", "repassadores")

tomadores_df = create_entity_table_from_junction_table(
    projeto_tomadores_df, "idUnico", "codigo"
)
executores_df = create_entity_table_from_junction_table(
    projeto_executores_df, "idUnico", "codigo"
)
repassadores_df = create_entity_table_from_junction_table(
    projeto_repassadores_df, "idUnico", "codigo"
)

instituicoes_df = pd.concat([tomadores_df, executores_df, repassadores_df])

# deduplicate indices
instituicoes_df = instituicoes_df.loc[~instituicoes_df.index.duplicated(keep="first")]

instituicoes_df.head(1)
Out[219]:
nome
codigo
25280 BANCO CENTRAL DO BRASIL
In [220]:
projeto_eixos_df = normalize_nested_column(df, "idUnico", "eixos")

eixos_df = create_entity_table_from_junction_table(projeto_eixos_df, "idUnico", "id")

eixos_df.head(1)
Out[220]:
descricao
id
1 Administrativo
In [221]:
projeto_tipos_df = normalize_nested_column(df, "idUnico", "tipos")

tipos_df = create_entity_table_from_junction_table(projeto_tipos_df, "idUnico", "id")

tipos_df.head(1)
Out[221]:
descricao idEixo
id
5 Administrativo 1
In [222]:
projeto_subtipos_df = normalize_nested_column(df, "idUnico", "subTipos")

subtipos_df = create_entity_table_from_junction_table(
    projeto_subtipos_df, "idUnico", "id"
)

subtipos_df.head(1)
Out[222]:
descricao idTipo
id
59 Obras em Imóveis de Uso Público 5
In [223]:
# nesse caso é one to many e nao many to many, entao nao precisa da tabela intermediaria

fontes_de_recurso_df = normalize_nested_column(
    df, "idUnico", "fontesDeRecurso"
).reset_index(drop=True)

# nesse caso estava certo o float
fontes_de_recurso_df["valorInvestimentoPrevisto"] = pd.to_numeric(
    fontes_de_recurso_df["valorInvestimentoPrevisto"], errors="coerce"
)

fontes_de_recurso_df.head(1)
Out[223]:
origem valorInvestimentoPrevisto idUnico
0 Federal 23427554.88 1828.53-16
In [224]:
df.drop(columns=nested_cols, inplace=True)

Foram criadas 5 novas tabelas com entidades:

  • instituicoes_df
  • eixos_df
  • tipos_df
  • subtipos_df
  • fontes_de_recurso_df

Além de 6 novas tabelas de junção:

  • projeto_tomadores_df
  • projeto_executores_df
  • projeto_repassadores_df
  • projeto_eixos_df
  • projeto_tipos_df
  • projeto_subtipos_df

2.5 Engenharia de características¶

Criação de novas colunas para fins de análise

  • textoTotal: concatenação de dados textuais para processamento de linguagem natural (NLP)
  • lat_viacep, lon_viacep, lat_ipedf, lon_ipedf: dados geográficos (obtidos por geocodificação via cep) usando dois métodos diferentes.
  • investimentoTotal: consolidação de fontesDeRecurso para análise quantitativa
  • investimentoFaixa: versão categórica de investimentoTotal por faixas.
  • isInvestimentoSimbolico: investimentos com valores menores que 1 real.
  • duracaoPrevista: duração prevista de projetos (via dataInicialPrevista, dataFinalPrevista)

textoTotal¶

In [225]:
text_cols = [
    "nome",
    "endereco",
    "descricao",
    "funcaoSocial",
    "metaGlobal",
    "especie",
    "natureza",
    "naturezaOutras",
    "situacao",
    "descPlanoNacionalPoliticaVinculado",
    "descPopulacaoBeneficiada",
    "observacoesPertinentes",
]


def make_text_total(row):
    text_total = []
    for x in row:
        if pd.notna(x):
            text_total.append(str(x))
    return "\n\n".join(text_total)


df["textoTotal"] = df[text_cols].apply(make_text_total, axis=1)

investimentoTotal¶

In [226]:
valor_por_id = (
    fontes_de_recurso_df.groupby("idUnico")["valorInvestimentoPrevisto"]
    .sum()
    .reset_index()
)

valor_por_id.rename(
    columns={"valorInvestimentoPrevisto": "investimentoTotal"}, inplace=True
)

df = df.merge(valor_por_id, on="idUnico", how="left", validate="one_to_one")

investimentoFaixa¶

In [227]:
bins = [
    0,
    1,
    10,
    100,
    1_000,
    10_000,
    100_000,
    1_000_000,
    10_000_000,
    100_000_000,
    1_000_000_000,
    np.inf,
]
labels = [
    "<1",
    "1-10",
    "10-100",
    "100-1k",
    "1k-10k",
    "10k-100k",
    "100k-1M",
    "1M-10M",
    "10M-100M",
    "100M-1B",
    ">1B",
]

df["investimentoFaixa"] = pd.cut(df["investimentoTotal"], bins=bins, labels=labels)
In [228]:
def plot_investment_ranges(df, palette):
    faixa_counts = df["investimentoFaixa"].value_counts().reset_index()
    faixa_counts.columns = ["investimentoFaixa", "count"]

    plt.figure(figsize=(10, 6))
    ax = sns.barplot(
        data=faixa_counts,
        x="investimentoFaixa",
        y="count",
        hue="investimentoFaixa",
        palette=[palette[0] for _ in range(len(faixa_counts))],
    )

    # custom palette creates multiple containers
    for container in ax.containers:
        ax.bar_label(container)  # type: ignore

    plt.xlabel("Faixa de Investimento (R$)")
    plt.ylabel("Projetos")
    plt.title("Projetos por Faixa de Investimento")
    plt.tight_layout()
    plt.show()


plot_investment_ranges(df, palette)
No description has been provided for this image

isInvestimentoSimbolico¶

In [229]:
df["isInvestimentoSimbolico"] = df["investimentoTotal"] <= 1
df["isInvestimentoSimbolico"].value_counts()
Out[229]:
isInvestimentoSimbolico
False    571
True     119
Name: count, dtype: int64

duracaoPrevista¶

In [230]:
df["duracaoPrevista"] = df["dataFinalPrevista"] - df["dataInicialPrevista"]
df["duracaoPrevista"] = df["duracaoPrevista"].dt.days
df["duracaoPrevista"] = df["duracaoPrevista"].astype(float)
df["duracaoPrevista"].describe()
Out[230]:
count     688.000000
mean      750.819767
std       688.251682
min         0.000000
25%       291.750000
50%       469.000000
75%      1090.250000
max      4827.000000
Name: duracaoPrevista, dtype: float64

Latitude e longitude¶

In [231]:
total = len(df)
missing = df["cep"].isna().sum()
print(f"Missing CEP values: {missing} out of {total} ({missing/total:.1%})")
ceps = df["cep"].dropna().unique()
Missing CEP values: 475 out of 690 (68.8%)
In [232]:
from geocode_ceps import cep_to_coords_viacep, cep_to_coords_ipedf

# para detalhes de obtenção dos valores, o arquivo `geocode_ceps.py`

# cep_to_coords_viacep(ceps.tolist(), "data/cep_coords_viacep.json")
# cep_to_coords_ipedf(ceps.tolist(), "data/cep_coords_ipedf.json")
In [233]:
with open("data/cep_coords_viacep.json") as f:
    data = json.load(f)
df_viacep = pd.DataFrame.from_dict(data, orient="index")

df_viacep.columns = ["latitude", "longitude"]

print(
    f"Null records: {df_viacep['latitude'].isna().sum()} out of {len(df_viacep)} ({df_viacep['latitude'].isna().sum()/len(df_viacep):.1%})"
)

with open("data/cep_coords_ipedf.json") as f:
    data = json.load(f)
df_ipedf = pd.DataFrame.from_dict(data, orient="index")

df_ipedf.columns = ["latitude", "longitude"]

print(
    f"Null records: {df_ipedf['latitude'].isna().sum()} out of {len(df_ipedf)} ({df_ipedf['latitude'].isna().sum()/len(df_ipedf):.1%})"
)

df = df.merge(
    df_viacep, left_on="cep", right_index=True, how="left", suffixes=("", "_viacep")
)

df = df.merge(
    df_ipedf, left_on="cep", right_index=True, how="left", suffixes=("", "_ipedf")
)

df = df.rename(
    columns={
        "latitude": "lat_viacep",
        "longitude": "lon_viacep",
        "latitude_ipedf": "lat_ipedf",
        "longitude_ipedf": "lon_ipedf",
    }
)

print(
    f"Records with coordinates from both sources: {df[['lon_viacep', 'lon_ipedf']].notna().all(axis=1).sum()}"
)

# Count records with and without coordinates
has_viacep = df[["lat_viacep", "lon_viacep"]].notna().all(axis=1)
has_ipedf = df[["lat_ipedf", "lon_ipedf"]].notna().all(axis=1)

print(
    f"Records with coordinates from either source: {(has_viacep | has_ipedf).sum()} ({(has_viacep | has_ipedf).sum()/len(df):.1%})"
)
Null records: 55 out of 88 (62.5%)
Null records: 47 out of 88 (53.4%)
Records with coordinates from both sources: 29
Records with coordinates from either source: 129 (18.7%)

Carregamento de dados¶

In [234]:
df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 690 entries, 0 to 689
Data columns (total 33 columns):
 #   Column                              Non-Null Count  Dtype         
---  ------                              --------------  -----         
 0   idUnico                             690 non-null    object        
 1   nome                                690 non-null    object        
 2   cep                                 215 non-null    object        
 3   endereco                            293 non-null    object        
 4   descricao                           690 non-null    object        
 5   funcaoSocial                        690 non-null    object        
 6   metaGlobal                          690 non-null    object        
 7   dataInicialPrevista                 688 non-null    datetime64[ns]
 8   dataFinalPrevista                   688 non-null    datetime64[ns]
 9   dataInicialEfetiva                  21 non-null     datetime64[ns]
 10  dataFinalEfetiva                    5 non-null      datetime64[ns]
 11  dataCadastro                        690 non-null    datetime64[ns]
 12  especie                             686 non-null    category      
 13  natureza                            690 non-null    category      
 14  naturezaOutras                      171 non-null    object        
 15  situacao                            690 non-null    category      
 16  descPlanoNacionalPoliticaVinculado  248 non-null    object        
 17  uf                                  690 non-null    category      
 18  qdtEmpregosGerados                  35 non-null     float64       
 19  descPopulacaoBeneficiada            144 non-null    object        
 20  populacaoBeneficiada                35 non-null     float64       
 21  observacoesPertinentes              106 non-null    object        
 22  isModeladaPorBim                    493 non-null    boolean       
 23  dataSituacao                        690 non-null    datetime64[ns]
 24  textoTotal                          690 non-null    object        
 25  investimentoTotal                   690 non-null    float64       
 26  investimentoFaixa                   687 non-null    category      
 27  isInvestimentoSimbolico             690 non-null    bool          
 28  duracaoPrevista                     688 non-null    float64       
 29  lat_viacep                          66 non-null     float64       
 30  lon_viacep                          66 non-null     float64       
 31  lat_ipedf                           92 non-null     float64       
 32  lon_ipedf                           92 non-null     float64       
dtypes: bool(1), boolean(1), category(5), datetime64[ns](6), float64(8), object(12)
memory usage: 146.9+ KB
In [235]:
with sqlite3.connect("projeto_investimento.db") as con:
    # main
    df.to_sql("projeto_investimento", con, if_exists="replace")

    # entity tables
    instituicoes_df.to_sql("instituicoes", con, if_exists="replace")
    eixos_df.to_sql("eixos", con, if_exists="replace")
    tipos_df.to_sql("tipos", con, if_exists="replace")
    subtipos_df.to_sql("subtipos", con, if_exists="replace")
    fontes_de_recurso_df.to_sql("fontes_de_recurso", con, if_exists="replace")

    # junction tables
    projeto_tomadores_df.to_sql("projeto_tomadores", con, if_exists="replace")
    projeto_executores_df.to_sql("projeto_executores", con, if_exists="replace")
    projeto_repassadores_df.to_sql("projeto_repassadores", con, if_exists="replace")
    projeto_eixos_df.to_sql("projeto_eixos", con, if_exists="replace")
    projeto_tipos_df.to_sql("projeto_tipos", con, if_exists="replace")
    projeto_subtipos_df.to_sql("projeto_subtipos", con, if_exists="replace")
In [236]:
# with sqlite3.connect("projeto_investimento.db") as con:
#     df = pd.read_sql_query("SELECT * FROM projeto_investimento", con)

3. Análise de dados¶

Informações básicas¶

Registros: 712 (após deduplicação de 834 registros originais)

Colunas:

  • texto

    • dados básicos: idUnico, nome, descricao, funcaoSocial, metaGlobal
    • descrições opcionais: naturezaOutras, descPlanoNacionalPoliticaVinculado, descPopulacaoBeneficiada, observacoesPertinentes
    • criada: textoTotal
  • geográfico

    • cep, endereco
    • criadas: lat_ipedf, lon_ipedf, lat_viacep, lon_viacep
  • numéricos

    • qdtEmpregosGerados
    • populacaoBeneficiada
    • criada: investimentoTotal
  • datas

    • dataInicialPrevista, dataFinalPrevista, dataInicialEfetiva, dataFinalEfetiva, dataCadastro, dataSituacao
    • criada: duracaoPrevista
  • categorias

    • natureza (Estudo, Obra, Outros, Projeto, Projeto de Investimento em Infraestrutura)
    • situacao (Cadastrada, Cancelada, Concluída, Em execução, Inacabada, Inativada, Paralisada)
    • especie (Ampliação, Construção, Fabricação, Máquinas e Equipamentos, Recuperação, Reforma)
    • isModeladaPorBim (Sim, Não)
    • uf (DF)
    • criada: investimentoFaixa (<1, 1-10, 10-100, 100-1k, 1k-10k, 10k-100k, 100k-1M, 1M-10M, 10M-100M, 100M-1B, >1B)
    • criada: isInvestimentoSimbolico (Sim, Não)

Colunas com dados aninhados (tomadores, executores, repassadores, eixos, tipos, subTipos, fontesDeRecurso) transformadas em outras tabelas após normalização. Também foram criadas tabelas de junção (projeto_tomadores, projeto_executores, projeto_repassadores, projeto_eixos, projeto_tipos, projeto_subtipos).

Tomadores, executores e repassadores partilham do mesmo sistema de códigos e portanto estão armazenados na mesma tabela.

Eixos, tipos, subtipos poderiam ser armazenados na mesma tabela com perda de normalização mas ganho em simplicidade.

Valores ausentes mais significativos:

  • datas efetivas (não previstas): 95% nulos
  • empregos gerados e populacao beneficiada: 95% nulos
  • dados geográficos (endereco e cep): 50% nulos
In [238]:
df.head()
Out[238]:
idUnico nome cep endereco descricao funcaoSocial metaGlobal dataInicialPrevista dataFinalPrevista dataInicialEfetiva dataFinalEfetiva dataCadastro especie natureza naturezaOutras situacao descPlanoNacionalPoliticaVinculado uf qdtEmpregosGerados descPopulacaoBeneficiada populacaoBeneficiada observacoesPertinentes isModeladaPorBim dataSituacao textoTotal investimentoTotal investimentoFaixa isInvestimentoSimbolico duracaoPrevista lat_viacep lon_viacep lat_ipedf lon_ipedf
0 1828.53-16 SISEG - Sistema integrado de segurança None None implantação dos sistemas de monitoramento por ... implantação dos sistemas de monitoramento por ... Modernização do sistema de segurança do Banco ... 2019-02-04 2024-02-06 NaT NaT 2021-05-12 Recuperação Projeto Cadastrada None DF NaN None NaN None <NA> 2021-05-12 SISEG - Sistema integrado de segurança\n\nimpl... 23427554.88 10M-100M False 1828.0 NaN NaN NaN NaN
1 1711.53-18 Reforma do espelho d'água do Edifício-Sede do ... None None Reforma do espelho d'água do Edifício-Sede do ... Reestabelecer a impermeabilização do espelho d... Recuperação do sistema de impermeabilização e ... 2021-01-04 2021-05-18 NaT NaT 2021-05-04 Reforma Obra Cadastrada None DF NaN None NaN None <NA> 2021-05-04 Reforma do espelho d'água do Edifício-Sede do ... 139328.54 100k-1M False 134.0 NaN NaN NaN NaN
2 1989.53-01 Elaboração de projetos arquitetônicos e de eng... None Campus Universitário Darcy Ribeiro, Brasília - DF Contratação de empresa especializada para elab... Obras para unidades acadêmicas de ensino e pes... Elaboração de projetos de engenharia e Arquite... 2021-05-18 2021-09-29 NaT NaT 2021-05-17 Construção Projeto None Cadastrada None DF NaN None NaN None <NA> 2021-05-17 Elaboração de projetos arquitetônicos e de eng... 1399545.52 1M-10M False 134.0 NaN NaN NaN NaN
3 2004.53-58 SISEG - Sistema integrado de segurança None None implantação dos sistemas de monitoramento por ... implantação dos sistemas de monitoramento por ... Modernização do sistema de segurança do Banco ... 2019-02-04 2024-02-06 NaT NaT 2021-05-18 Recuperação Projeto Cadastrada None DF NaN None NaN None <NA> 2021-05-18 SISEG - Sistema integrado de segurança\n\nimpl... 23427554.88 10M-100M False 1828.0 NaN NaN NaN NaN
4 2617.53-02 CENTRO DE FORMAÇÃO TECNOLOGICA - CFT - CAMPUS ... None Área Especial n° 01, Quadra 16, Cidade do Auto... construção um galpão com pé direito alto, que ... atender a comunidade acadêmica do Campus Estru... aumentar a oferta de cursos técnicos/tecnológi... 2020-12-01 2021-10-27 NaT NaT 2021-06-18 Construção Obra Cadastrada triplicar as matrículas da educação profission... DF NaN None NaN None <NA> 2021-06-18 CENTRO DE FORMAÇÃO TECNOLOGICA - CFT - CAMPUS ... 1862560.00 1M-10M False 330.0 NaN NaN NaN NaN

Qualidade¶

Em questão de limpeza do dados, temos vários problemas:

  1. Valores ausentes: 95% de nulos em campos importantes
  2. Duplicatas: 87 registros duplicados
  3. Encoding: 18 projetos com erro de encoding
  4. Dados de teste: 22 registros falsos (Ronald, Teste)
  5. Falta de validação: CEPs preenchidos com "1", espaços em branco
  6. Valores suspeitos: organizações com mais de um código, projetos milionários com duração de zero dias
  7. Dados simbólicos: Investimentos de R$ 0,01.

Para problemas mais detalhados, ver apendices/qualidade_detalhes.txt

Esses problemas possivelmente indicam:

  1. Falta de validação e padronização dos dados (e.g. CEP)
  2. Alteração do formulário com o passar do tempo
  3. Confusão por parte dos usuários ao preencher os dados
  4. Campos do formulário que não se adequam bem à realidade dos projetos

Categorias¶

In [239]:
def plot_bim_pie(df: pd.DataFrame, palette: list) -> None:
    # Convert boolean to labels with proper ordering
    df["bim_label"] = (
        df["isModeladaPorBim"].map({True: "Sim", False: "Não"}).fillna("Sem dados")
    )
    bim_counts = df["bim_label"].value_counts()

    fig, ax = plt.subplots(figsize=(4, 4))

    # Create combined labels with percentages
    combined_labels = [
        f"{label}\n({pct:.1f}%)"
        for label, count, pct in zip(
            bim_counts.index,
            bim_counts.values,
            bim_counts.values / bim_counts.sum() * 100,
        )
    ]

    wedges, texts, autotexts = ax.pie(
        bim_counts.values,
        labels=combined_labels,  # Use combined labels
        colors=palette,
        autopct="",  # Remove separate percentage text
        labeldistance=1.25,
        wedgeprops=dict(width=0.5, linewidth=0),
    )

    for text in texts:
        text.set_ha("center")
        text.set_ha("center")

    ax.set_title("Projeto modelado usando BIM\n(Building Information Modeling)")
    plt.tight_layout()
    plt.show()


plot_bim_pie(df, palette)
No description has been provided for this image
In [257]:
def make_barh_plot(
    df: pd.DataFrame,
    column: str,
    title: str,
    color: str,
    extra_space: float = 1,
    show_percentages: bool = True,
    labels_inside: bool = False,
):
    plt.figure(figsize=(10, 6))

    # Get counts and percentages
    value_counts = df[column].value_counts(dropna=False)
    percentages = (value_counts / len(df)) * 100

    # Create DataFrame with both counts and percentages
    value_counts_df = pd.DataFrame(
        {"count": value_counts, column: value_counts.index, "percentage": percentages}
    ).reset_index(drop=True)

    ax = sns.barplot(
        data=value_counts_df,
        x="count",
        y=column,
        hue=column,
        orient="h",
        order=value_counts_df[column],
        palette=[color for _ in range(len(value_counts_df))],
        legend=False,
    )

    # Add labels with count and optionally percentage
    for i, v in enumerate(value_counts_df["count"]):
        if show_percentages:
            label = f"{int(v)} ({percentages.iloc[i]:.1f}%)"
        else:
            label = f"{int(v)}"

        # Position labels inside or outside bars based on labels_inside parameter
        if labels_inside:
            x_pos = v - 5  # Shift inside
            ha = "right"
        else:
            x_pos = v + 5  # Shift outside
            ha = "left"

        ax.text(x_pos, i, label, va="center", ha=ha)

    # Extend x-axis to accommodate labels if they're outside
    max_val = value_counts_df["count"].max()
    if not labels_inside:
        ax.set_xlim(0, max_val * extra_space)
    else:
        ax.set_xlim(0, max_val)

    plt.title(title)
    plt.xlabel("Quantidade")
    plt.ylabel("")
    plt.tight_layout()
    plt.show()
In [258]:
make_barh_plot(df, "natureza", "Natureza dos projetos", palette[0], extra_space=1.55)

make_barh_plot(df, "situacao", "Situação dos projetos", palette[1], extra_space=1.14)

# handle nulls
especie_vis = df.copy()
especie_vis["especie"] = (
    especie_vis["especie"].cat.add_categories("Sem dados").fillna("Sem dados")
)
make_barh_plot(
    especie_vis, "especie", "Espécies de projetos", palette[2], extra_space=1.15
)
No description has been provided for this image
No description has been provided for this image
No description has been provided for this image
  • BIM: Apenas 3.5% dos projetos usou BIM, enquanto 66.2% não usou.
  • Natureza dos projetos: A grande maioria dos itens (73%) é classificado como obra, e não como projeto ou projeto de investimento em infraestrutura.
  • Situação dos projetos: 76% projetos estão cadastrados, com 11% em execução, 8% concluídos e 3% inativados, cancelados, paralisados ou inacabados.
  • Espécies de projetos: 44% construção de novos projetos e 54% reforma, ampliação ou recuperação de projetos existentes.

Investimento¶

Como as variáveis quantitativas qdtEmpregosGerados e populacaoBeneficiada só estão preenchidas em 5% dos casos, evitamos analisá-las.

In [259]:
# Plot normal scale
plt.figure(figsize=(10, 4))
plt.subplot(1, 2, 1)
sns.histplot(
    data=df[~df["isInvestimentoSimbolico"]],
    x="investimentoTotal",
    bins=25,
    color=palette[0],
)
plt.xlabel("Investimento (R$)")
plt.ylabel("Projetos")
plt.title("Projetos por Investimento")

# Plot log scale
plt.subplot(1, 2, 2)
sns.histplot(
    data=df[~df["isInvestimentoSimbolico"]],
    x="investimentoTotal",
    bins=25,
    color=palette[0],
    log_scale=True,
)
plt.xlabel("Investimento (R$)")
plt.ylabel("Projetos")
plt.title("Projetos por Investimento (escala logarítmica)")

plt.tight_layout()
plt.show()
No description has been provided for this image
In [243]:
df[~df["isInvestimentoSimbolico"]]["investimentoTotal"].describe().apply(
    lambda x: f"{x:,.2f}"
)
Out[243]:
count            571.00
mean      13,871,387.02
std       51,931,177.66
min            4,684.45
25%          667,980.23
50%        2,085,189.23
75%        8,402,637.63
max      839,664,954.32
Name: investimentoTotal, dtype: object
In [244]:
def calculate_iqr(data: pd.Series) -> float:
    q75 = data.quantile(0.75)
    q25 = data.quantile(0.25)
    return q75 - q25


valores = df[~df["isInvestimentoSimbolico"]]["investimentoTotal"]
iqr = calculate_iqr(valores)
print(f"IQR: R$ {iqr:,.2f}")
IQR: R$ 7,734,657.40
In [245]:
# Calcular porcentagem de projetos com investimento simbólico vs total
total_projetos = len(df)
projetos_significativos = len(df[~df["isInvestimentoSimbolico"]])
projetos_simbolicos = len(df[df["isInvestimentoSimbolico"]])

print(
    f"Projetos com investimento simbólico: {projetos_simbolicos} ({projetos_simbolicos/total_projetos*100:.1f}%)"
)
Projetos com investimento simbólico: 119 (17.2%)

17.2% dos projetos possuem investimento simbólico, isto é, estão registrados com investimentos de menos de um real.

Considerando apenas os investimentos significativos (571 projetos), vemos uma distribuição aproximadamente log-normal (comum com este tipo de dado não-negativo, como dinheiro). Isso significa que grandes investimentos são exponencialmente mais raros que os casos de investimento menor. Assim, isso também indica que os valores mais altos não são outliers, são parte esperada da distribuição log-normal.

Isso também significa que a média (R$ 13,9 milhões) é pouco informativa sobre os dados, pois é muito influenciada pelos valores extremos. Podemos usar mediana e intervalo interquartil (IQR) para ter uma ideia melhor da distribuição.

Os dados apresentam, aproximadamente:

  • Valor mínimo de R$ 4,7 mil
  • Valor máximo de R$ 840 milhões
  • Mediana (valor que divide os dados ao meio): R$ 2,1 milhões
  • IQR de R$ 7,7 milhões, ou seja, cerca de 50% dos projetos estão entre R$ 668 mil e R$ 8,4 milhões

Datas¶

Devido à falta de dados para as datas reais, podemos comparar apenas as datas previstas.

In [260]:
date_cols = [
    "dataInicialPrevista",
    "dataFinalPrevista",
    "dataInicialEfetiva",
    "dataFinalEfetiva",
    "dataCadastro",
    "dataSituacao",
]
In [300]:
fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(8, 4))

# Plot normal scale
sns.histplot(
    data=df["duracaoPrevista"].divide(365),
    bins=50,
    color=palette[0],
    ax=ax1
)
ax1.set_xlabel("Duração Prevista (anos)")
ax1.set_ylabel("Projetos")
ax1.set_title("Projetos por Duração Prevista")

# Plot log scale
sns.histplot(
    data=df["duracaoPrevista"].divide(365),
    bins=50,
    color=palette[0],
    log_scale=True,
    ax=ax2
)
ax2.set_xlabel("Duração Prevista (anos)")
ax2.set_ylabel("Projetos")
ax2.set_title("Projetos por Duração Prevista\n(escala logarítmica)")

plt.tight_layout()
plt.show()
No description has been provided for this image
In [275]:
df["duracaoPrevista"].divide(365).describe().apply(lambda x: f"{x:,.2f}")
Out[275]:
count    688.00
mean       2.06
std        1.89
min        0.00
25%        0.80
50%        1.28
75%        2.99
max       13.22
Name: duracaoPrevista, dtype: object
In [277]:
print("IQR:", calculate_iqr(df["duracaoPrevista"].divide(365)))
IQR: 2.187671232876712

A duração prevista também apresenta uma distribuição logarítmica, com os valores:

  • Valor mínimo de 0 dias
  • Valor máximo de 13 anos
  • Mediana: 1,2 anos
  • Cerca de 50% dos projetos têm duração entre 0,8 e 3 anos
In [ ]:
# valores baixos
df[["descricao", "duracaoPrevista", "investimentoFaixa"]].sort_values(
    by="duracaoPrevista", ascending=True
).head()
Out[ ]:
descricao duracaoPrevista investimentoFaixa
304 CONSTRUÇÃO DE UNIDADE DE ATENÇÃO ESPECIALIZADA... 0 days 1M-10M
326 CONSTRUÇÃO DE UNIDADE DE ATENÇÃO ESPECIALIZADA... 0 days 1M-10M
331 CONSTRUÇÃO DE UNIDADE BÁSICA DE SAÚDE 0 days 1M-10M
9 Obra no Batalhão Escola e Pronto Emprego para ... 0 days 10M-100M
287 CONSTRUÇÃO DE UNIDADE DE ATENÇÃO ESPECIALIZADA... 1 days 1M-10M

Encontramos aqui alguns valores suspeitos, em que projetos com investimentos milionários tem duração prevista de zero dias.

A maior parte dos projetos possui situação de 'cadastrado', com apenas 81 (11%) projetos estarem em situação 'em execução'. Apesar disso, apenas 4 projetos possuem data de início no futuro. A documentação dos dados não entra em detalhes sobre o significado destas categorias ou a atualização dos dados.

In [ ]:
def plot_date_distributions(df, palette):
    from matplotlib import dates as mdates

    # Create subplots for date distributions
    fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(12, 5), sharey=True)

    # Plot initial dates
    sns.histplot(data=df, x="dataInicialPrevista", bins=25, color=palette[0], ax=ax1)
    ax1.set_title("Datas Iniciais Previstas")
    ax1.set_ylabel("Frequência")
    ax1.set_xlabel("")
    ax1.tick_params(axis="x", rotation=45)

    # Plot final dates
    sns.histplot(data=df, x="dataFinalPrevista", bins=25, color=palette[0], ax=ax2)
    ax2.set_title("Datas Finais Previstas")
    ax2.set_ylabel("")
    ax2.set_xlabel("")
    ax2.tick_params(axis="x", rotation=45)

    # Set more frequent x-axis ticks
    ax1.xaxis.set_major_locator(mdates.YearLocator(2))  # Show every 2 years
    ax2.xaxis.set_major_locator(mdates.YearLocator(2))  # Show every 2 years

    # Format dates on x-axis
    ax1.xaxis.set_major_formatter(mdates.DateFormatter("%Y"))
    ax2.xaxis.set_major_formatter(mdates.DateFormatter("%Y"))

    # Adjust layout
    plt.tight_layout()
    plt.show()


plot_date_distributions(df, palette)
No description has been provided for this image

Há dados iniciando em 2004, a projetos com final prevista em 2032. A maiora dos projetos previa iniciar após 2020, com a maior parte terminando antes de 2028. Houve, também, um pico de projetos curtos em 2014.

In [282]:
# Preparar dados
data_for_reg = df[~df["isInvestimentoSimbolico"]].copy()
data_for_reg = data_for_reg[
    ["idUnico", "descricao", "duracaoPrevista", "investimentoTotal"]
].dropna()

data_for_reg["investimentoTotal_log"] = np.log(data_for_reg["investimentoTotal"])
data_for_reg["duracaoPrevista_log"] = np.log(data_for_reg["duracaoPrevista"])

fig, axes = plt.subplots(1, 2, figsize=(15, 8))

# 1. Escala original
sns.regplot(
    data=data_for_reg,
    x="duracaoPrevista",
    y="investimentoTotal",
    scatter_kws={"alpha": 0.5},
    line_kws={"color": "red"},
    ax=axes[0],
)
axes[0].set_title("Relação entre Duração e Investimento")
axes[0].set_xlabel("Duração Prevista (dias)")
axes[0].set_ylabel("Investimento Total (R$)")

# 2. Log-log
sns.regplot(
    data=data_for_reg,
    x="duracaoPrevista_log",
    y="investimentoTotal_log",
    scatter_kws={"alpha": 0.5},
    line_kws={"color": "red"},
    ax=axes[1],
)
axes[1].set_title("Relação entre Duração e Investimento (log-log)")
axes[1].set_xlabel("Duração Prevista (log dias)")
axes[1].set_ylabel("Investimento Total (log R$)")

plt.tight_layout()
plt.show()
/home/noah-art3mis/projects/takehome-lablivre-analysis/.venv/lib/python3.10/site-packages/pandas/core/arraylike.py:399: RuntimeWarning: divide by zero encountered in log
  result = getattr(ufunc, method)(*inputs, **kwargs)
/home/noah-art3mis/projects/takehome-lablivre-analysis/.venv/lib/python3.10/site-packages/numpy/_core/function_base.py:162: RuntimeWarning: invalid value encountered in multiply
  y *= step
/home/noah-art3mis/projects/takehome-lablivre-analysis/.venv/lib/python3.10/site-packages/numpy/_core/function_base.py:172: RuntimeWarning: invalid value encountered in add
  y += start
/home/noah-art3mis/projects/takehome-lablivre-analysis/.venv/lib/python3.10/site-packages/numpy/lib/_nanfunctions_impl.py:1620: RuntimeWarning: All-NaN slice encountered
  return fnb._ureduce(a,
No description has been provided for this image
In [287]:
pg.corr(
    df[~df["isInvestimentoSimbolico"]]["duracaoPrevista"],
    df[~df["isInvestimentoSimbolico"]]["investimentoTotal"],
    method="spearman",  # nao parametrico
)
Out[287]:
n r CI95% p-val power
spearman 569 0.648612 [0.6, 0.69] 3.130230e-69 1.0
In [293]:
X = sm.add_constant(data_for_reg["duracaoPrevista_log"])
y = data_for_reg["investimentoTotal_log"]
model = sm.OLS(y, X).fit()

data_for_reg["residuos"] = model.resid
data_for_reg["residuos_abs"] = np.abs(model.resid)

data_for_reg.nlargest(10, "residuos_abs").assign(
    duracaoPrevista_years=lambda x: x["duracaoPrevista"],
    investimentoTotal_k=lambda x: x["investimentoTotal"].apply(lambda x: f"{x:,.2f}"),
)[["idUnico", "descricao", "duracaoPrevista", "investimentoTotal_k", "residuos"]].round(
    {"duracaoPrevista_years": 1, "investimentoTotal_k": 0, "residuos": 1}
)
Out[293]:
idUnico descricao duracaoPrevista investimentoTotal_k residuos
287 46882.53-02 CONSTRUÇÃO DE UNIDADE DE ATENÇÃO ESPECIALIZADA... 1.0 2,493,000.00 8.1
374 43724.53-06 Contratação de empresa do ramo de engenharia/a... 1081.0 4,684.45 -7.1
117 557.53-69 Projeto Estratégico do SISFRON, cujo objeto é ... 579.0 839,664,954.32 5.8
69 10613.53-07 Prestação de serviços de disponibilização de a... 1799.0 47,700.00 -5.4
646 5223.53-75 ELABORAÇÃO DE PROJETOS BÁSICO E EXECUTIVO DE P... 789.0 20,305.41 -5.2
516 39020.52-43 execução das obras de adequação de capacidade,... 629.0 352,994,609.29 4.9
549 28451.53-40 ADEQUACAO DE TRECHO RODOVIARIO - ENTRONCAMENTO... 755.0 359,130,057.13 4.6
298 1729.53-04 Prestação de serviços de elaboração de Antepro... 359.0 19,560.00 -4.2
420 57958.53-86 Celebrar parcerias referentes ao PRONER 364.0 20,000.00 -4.2
389 42995.53-70 Contratação de empresa especializada em arquit... 352.0 21,560.99 -4.1

Considerando apenas os projetos com investimento significativo (n=569), encontramos uma correlação moderada a forte entre a duração prevista e o investimento total (ρ=0.64, IC95%=[0.60, 0.69], p<0.001). Isso é esperado, pois projetos maiores tendem a durar mais.

Com esses dados podemos calcular os projetos mais anômalos: curtos que custam muito ou demorados que custam pouco. Isso é feito usando os resíduos de uma regressão linear, que são mostrados acima. Estes registros mais anômalos são:

ID Descrição Duração Investimento
46882.53-02 CONSTRUÇÃO DE UNIDADE DE ATENÇÃO ESPECIALIZADA... 1 dia R$ 2.493.000,00
43724.53-06 Contratação de empresa do ramo de engenharia/a... 3 anos R$ 4.684,45
557.53-69 Projeto Estratégico do SISFRON... 1.6 anos R$ 839.664.954,32
10613.53-07 Prestação de serviços de disponibilização de a... 4.9 anos R$ 47.700,00
5223.53-75 ELABORAÇÃO DE PROJETOS BÁSICO E EXECUTIVO DE P... 2.2 anos R$ 20.305,41
39020.52-43 execução das obras de adequação de capacidade,... 1.7 anos R$ 352.994.609,29
28451.53-40 ADEQUACAO DE TRECHO RODOVIARIO - ENTRONCAMENTO... 2.1 anos R$ 359.130.057,13

Dados geográficos (GIS)¶

Mesmo utilizando APIs oficiais, houve dificuldade em obter coordenadas a partir dos CEPs. Ambos os métodos localizaram apenas cerca de metade dos 88 CEPs presentes nos dados.

Apenas 18% de todos os registros possuem representação no mapa — uma amostra que reflete um viés de seleção em duas etapas: primeiro, dos registros que contêm CEP e, depois, dos CEPs que puderam ser convertidos em coordenadas.

Entre os 88 CEPs válidos, as ferramentas de geocodificação do governo encontraram coordenadas para 41 e 33 deles, respectivamente; 29 CEPs foram localizados por ambas — o que não implica necessariamente que as coordenadas coincidem (por exemplo, o CEP 71205-050).

Nos casos com duas coordenadas disponíveis, optou-se pelos dados do IPEDF, uma vez que essa fonte obtém as coordenadas diretamente do CEP, enquanto o ViaCEP realiza a conversão de CEP para endereço, seguida de geocodificação via Nominatim.

In [ ]:
def create_investment_map(df):
    # Create map centered on DF
    m = folium.Map(location=[-15.7942, -47.8822], zoom_start=10)

    # Define single color for all markers
    COLOR = "#412355"  # Purple (LabLivre palette)

    # Prepare data with proper coordinate selection
    df_map = df[
        [
            "cep",
            "nome",
            "descricao",
            "lat_ipedf",
            "lon_ipedf",
            "lat_viacep",
            "lon_viacep",
            "investimentoTotal",
        ]
    ].copy()

    # Add markers for each project with coordinates
    for idx, row in df_map.iterrows():
        lat_ipedf = row["lat_ipedf"]
        lon_ipedf = row["lon_ipedf"]
        lat_viacep = row["lat_viacep"]
        lon_viacep = row["lon_viacep"]

        # Determine which coordinates to use (prioritize IPEDF)
        if pd.notna(lat_ipedf) and pd.notna(lon_ipedf):
            lat, lon = lat_ipedf, lon_ipedf
            source = "IPEDF"
        elif pd.notna(lat_viacep) and pd.notna(lon_viacep):
            lat, lon = lat_viacep, lon_viacep
            source = "ViaCEP"
        else:
            # No coordinates available
            continue

        # Prepare popup text
        popup_text = (
            f"""
        <b>CEP:</b> {row['cep']}<br>
        <b>Fonte:</b> {source}<br>
        <b>Nome:</b> {row['nome'][:50]}...<br>
        <b>Investimento:</b> R$ {row['investimentoTotal']:,.2f}
        """
            if pd.notna(row["investimentoTotal"])
            else f"""
        <b>CEP:</b> {row['cep']}<br>
        <b>Fonte:</b> {source}<br>
        <b>Nome:</b> {row['nome'][:50]}...
        """
        )

        # Add marker with radius scaled by investment amount
        folium.CircleMarker(
            location=[lat, lon],
            radius=(
                np.sqrt(row["investimentoTotal"] / 100000)
                if pd.notna(row["investimentoTotal"])
                else 5
            ),
            color=COLOR,
            fill=True,
            fillColor=COLOR,
            fillOpacity=0.7,
            popup=folium.Popup(popup_text, max_width=300),
            tooltip=f"{row['cep']} - {source}",
        ).add_to(m)

    return m


m = create_investment_map(df)
m
Out[ ]:
Make this Notebook Trusted to load map: File -> Trust Notebook

O mapa é interativo e revela uma concentração de investimentos na região da Asa Sul. Também há presença significativa em Ceilândia, Gama e Planaltina -- áreas que, em geral, possuem maior densidade populacional. Fora dessas regiões, observam-se poucos ou nenhum investimento registrado (como em São Sebastião, por exemplo).

Parte da ausência de pontos no mapa pode estar relacionada tanto ao processo de seleção dos dados quanto à disponibilidade dos CEPs nas APIs de geocodificação utilizadas.

Processamento de linguagem natural (NLP)¶

Podemos usar as colunas que contem texto para analisar padrões semânticos nos dados. Aqui, usamos embeddings com redução de dimensionalidade e agrupamento (clustering).

O modelo de embedding se beneficia de ser usado em um computador com GPU. A produção dos embeddings foi feita na nuvem e salvos em um arquivo que é aberto aqui.

In [ ]:
# df[['idUnico', 'textoTotal']].to_parquet('data/projetos_sem_vetores.parquet')
In [ ]:
# # fazer isso aqui usando uma GPU

# def get_embeddings(df: pd.DataFrame, model: SentenceTransformer) -> pd.DataFrame:
#     # https://huggingface.co/google/embeddinggemma-300m

#     # requires HF_TOKEN env variable
#     from dotenv import load_dotenv
#     load_dotenv()


#     # gated model: only unlocks if you sign the terms and conditions consent form in the huggingface model page
#     from sentence_transformers import SentenceTransformer
#     model = SentenceTransformer("google/embeddinggemma-300m")

#     sentences = df["textoTotal"].tolist()

#     embeddings = model.encode(
#         sentences=sentences,
#         prompt_name="Clustering", # gemma specific
#         show_progress_bar=True,
#         convert_to_numpy=True,
#     )

#     sentences_with_embeddings = pd.DataFrame(
#         {"texto": sentences, "embeddings": embeddings.tolist()}
#     )
#     df_with_embeddings = pd.merge(
#         df, sentences_with_embeddings, left_on="textoTotal", right_on="texto"
#     )
#     df_with_embeddings = df_with_embeddings.drop("texto", axis=1)
#     return df_with_embeddings


# textos_df = pd.read_parquet('data/projetos_sem_vetores.parquet')
# df_with_embeddings = get_embeddings(textos_df, model)
# df_with_embeddings.to_parquet('data/projetos_com_vetores.parquet')
In [ ]:
embeddings = pd.read_parquet("data/projetos_com_vetores.parquet")
df = df.merge(embeddings.drop("textoTotal", axis=1), on="idUnico", how="left")
In [ ]:
def umap_df(
    df: pd.DataFrame, vectors_col: str, dimension_names: list[str]
) -> pd.DataFrame:
    # Based on https://umap-learn.readthedocs.io/en/latest/

    reducer = umap.UMAP(
        n_components=2,
        n_neighbors=15,  # Default from documentation
        min_dist=0.1,  # Default from documentation
        metric="euclidean",  # Default metric
        random_state=42,
        verbose=False,
    )

    umap_components = reducer.fit_transform(df[vectors_col].tolist())
    umap_df = pd.DataFrame(umap_components, columns=dimension_names)  # type: ignore
    return pd.concat([df, umap_df], axis=1)


def hdbscan_df(
    df: pd.DataFrame, dimension_names: list[str], min_cluster_size: int
) -> pd.DataFrame:
    # https://github.com/scikit-learn-contrib/hdbscan

    clusterer = hdbscan.HDBSCAN(min_cluster_size)
    clusters = clusterer.fit_predict(df[dimension_names])
    df["cluster"] = clusters
    df["cluster"] = df["cluster"].astype(str)

    return df
In [ ]:
dimension_names = ["UMAP_1", "UMAP_2"]
df = umap_df(df, "embeddings", dimension_names)
df = hdbscan_df(df, dimension_names, min_cluster_size=20)
/home/noah-art3mis/projects/takehome-lablivre-analysis/.venv/lib/python3.10/site-packages/umap/umap_.py:1952: UserWarning: n_jobs value 1 overridden to 1 by setting random_state. Use no seed for parallelism.
  warn(
/home/noah-art3mis/projects/takehome-lablivre-analysis/.venv/lib/python3.10/site-packages/sklearn/utils/deprecation.py:132: FutureWarning: 'force_all_finite' was renamed to 'ensure_all_finite' in 1.6 and will be removed in 1.8.
  warnings.warn(
/home/noah-art3mis/projects/takehome-lablivre-analysis/.venv/lib/python3.10/site-packages/sklearn/utils/deprecation.py:132: FutureWarning: 'force_all_finite' was renamed to 'ensure_all_finite' in 1.6 and will be removed in 1.8.
  warnings.warn(
In [ ]:
def plot_embeddings_interactive(
    df: pd.DataFrame, dimension_names: list[str], hue: str
) -> alt.Chart:
    chart = (
        alt.Chart(df)
        .mark_circle(size=100, opacity=0.7, strokeWidth=0.5, stroke="white")
        .encode(
            x=alt.X(dimension_names[0], title="UMAP1"),
            y=alt.Y(dimension_names[1], title="UMAP2"),
            color=alt.Color(
                hue,
                scale=alt.Scale(scheme="tableau10"),
                legend=alt.Legend(title="Cluster"),
            ),
            tooltip=[
                alt.Tooltip(hue, title="Grupo"),
                alt.Tooltip("idUnico", title="ID"),
                alt.Tooltip("descricao", title="Descrição"),
                # alt.Tooltip("investimentoTotal", title="Investimento"),
                alt.Tooltip("duracaoPrevista", title="Duração Prevista"),
                alt.Tooltip("natureza", title="Natureza"),
                # alt.Tooltip("situacao", title="Situação"),
                alt.Tooltip("especie", title="Espécie"),
                alt.Tooltip("investimentoFaixa", title="Investimento Faixa"),
                # alt.Tooltip("dataInicialPrevista", title="Data Início Prevista"),
                # alt.Tooltip("dataFinalPrevista", title="Data Final Prevista"),
            ],
        )
        .properties(
            width=600,
            height=400,
            title="Representação UMAP dos embeddings dos projetos com agrupamento (HDBSCAN)",
        )
        .interactive()
    )  # Enables pan and zoom

    return chart
In [ ]:
# altair does not recognise timedelta64[ns]
df_nlp_vis = df.copy()
df_nlp_vis["duracaoPrevista"] = df_nlp_vis["duracaoPrevista"].dt.days

plot_embeddings_interactive(df_nlp_vis, dimension_names, hue="cluster")
Out[ ]:

Podemos utilizar este mapa interativo para explorar os dados. Desta forma, observando as descrições dos itens em cada grupo, podemos criar uma classificação qualitativa:

Grupo Categoria
0 Cobertura de quadra escolar
1 Educação básica
2 Saúde
3 Energia
4 Educação superior
5 Assessoria/terceirização
6 Engenharia civil
7 Transporte
8 Serviço social
-1 Outros
In [ ]:
cluster_names = {
    0: "Cobertura de quadra escolar",
    1: "Educação básica",
    2: "Saúde",
    3: "Energia",
    4: "Educação superior",
    5: "Assessoria/terceirização",
    6: "Engenharia civil",
    7: "Transporte",
    8: "Serviço social",
    -1: "Outros",
}

df["clusterNome"] = df["cluster"].astype(str).map(lambda x: cluster_names[int(x)])
df[["idUnico", "cluster", "clusterNome"]]
Out[ ]:
idUnico cluster clusterNome
0 1828.53-16 -1 Outros
1 1828.53-16 -1 Outros
2 1711.53-18 6 Engenharia civil
3 1711.53-18 6 Engenharia civil
4 1989.53-01 4 Educação superior
... ... ... ...
703 34791.53-87 -1 Outros
704 28755.53-80 6 Engenharia civil
705 15636.53-06 6 Engenharia civil
706 15266.53-05 6 Engenharia civil
707 15557.53-09 -1 Outros

708 rows × 3 columns

In [ ]:
make_barh_plot(
    df,
    "clusterNome",
    "Grupos de projetos",
    palette[1],
    extra_space=1.17,
    show_percentages=True,
)
No description has been provided for this image
In [ ]:
def set_categorical_order(df_to_order, df_reference, column):
    order = df_reference[column].value_counts().index
    df_to_order[column] = pd.Categorical(
        df_to_order[column], categories=order, ordered=True
    )
    return df_to_order
In [ ]:
# Agregar investimento total por grupo (clusterNome)
cluster_investimentos_df = (
    df.groupby("clusterNome", dropna=False)["investimentoTotal"]
    .sum()
    .reset_index()
    .rename(columns={"investimentoTotal": "investimentoPorArea"})
)

# Converter para milhões e calcular percentuais
cluster_investimentos_df["investimentoPorArea"] = (
    cluster_investimentos_df["investimentoPorArea"] / 1_000_000
)
total_investimento = cluster_investimentos_df["investimentoPorArea"].sum()
cluster_investimentos_df["percentual"] = (
    cluster_investimentos_df["investimentoPorArea"] / total_investimento
) * 100

# Usar a mesma ordem do gráfico anterior
order = df["clusterNome"].value_counts().index
cluster_investimentos_df["clusterNome"] = pd.Categorical(
    cluster_investimentos_df["clusterNome"], categories=order, ordered=True
)
cluster_investimentos_df = cluster_investimentos_df.sort_values("clusterNome")

# Criar figura com um subplot
plt.figure(figsize=(10, 6))

# Plotar valores absolutos
ax = sns.barplot(
    data=cluster_investimentos_df,
    x="investimentoPorArea",
    y="clusterNome",
    color=palette[0],
    orient="h",
)

# Rótulos com valores absolutos e percentuais
for i, (v, p) in enumerate(
    zip(
        cluster_investimentos_df["investimentoPorArea"],
        cluster_investimentos_df["percentual"],
    )
):
    ax.text(v * 1.01, i, f"R$ {v:,.0f}M ({p:.1f}%)", va="center", ha="left")

ax.set_title("Investimento total por grupo de projetos")
ax.set_xlabel("Investimento total (milhões R$)")
ax.set_ylabel("")

max_val = cluster_investimentos_df["investimentoPorArea"].max()
ax.set_xlim(0, max_val * 1.24)

plt.tight_layout()
plt.show()

make_barh_plot(
    df,
    "clusterNome",
    "Grupos de projetos",
    palette[1],
    extra_space=1.17,
    show_percentages=True,
)
No description has been provided for this image
No description has been provided for this image
In [ ]:
f"Total de investimentos: R$ {df['investimentoTotal'].sum():,.2f}"
Out[ ]:
'Total de investimentos: R$ 8,402,336,549.57'
In [ ]:
# Calcular mediana de caracteres da descrição por cluster
desc_len = (
    df.groupby("clusterNome")
    .agg(
        n_projetos=("idUnico", "nunique"),
        mediana_caracteres=("descricao", lambda x: x.str.len().median()),
    )
    .round(1)
)

# Reordenar usando a função set_categorical_order
desc_len = set_categorical_order(desc_len.reset_index(), df, "clusterNome")

desc_len = desc_len.sort_values("mediana_caracteres", ascending=True)

plt.figure(figsize=(10, 6))
ax = sns.barplot(
    data=desc_len, x="mediana_caracteres", y="clusterNome", color=palette[0], orient="h"
)

# Adicionar rótulos com valores
for bar in ax.patches:
    width = bar.get_width()
    y = bar.get_y() + bar.get_height() / 2
    ax.text(width * 1.01, y, f"{width:,.0f}", va="center", ha="left")

ax.set_title("Mediana de caracteres na descrição por grupo de projetos")
ax.set_xlabel("Número mediano de caracteres")
ax.set_ylabel("")

plt.tight_layout()
plt.show()
No description has been provided for this image
In [ ]:
# Create butterfly plot comparing number of projects vs investment by cluster
def create_butterfly_plot(df, palette):
    """
    Create a butterfly plot comparing number of projects vs investment by cluster
    """
    # Prepare data for butterfly plot
    butterfly_data = (
        df.groupby("clusterNome")
        .agg(
            {
                "idUnico": "nunique",  # Count unique projects
                "investimentoTotal": "sum",  # Sum investment
            }
        )
        .reset_index()
    )

    # Calculate percentages
    total_projects = butterfly_data["idUnico"].sum()
    total_investment = butterfly_data["investimentoTotal"].sum()

    butterfly_data["projetos_pct"] = (butterfly_data["idUnico"] / total_projects) * 100
    butterfly_data["investimento_pct"] = (
        butterfly_data["investimentoTotal"] / total_investment
    ) * 100

    # Sort by number of projects (descending)
    butterfly_data = butterfly_data.sort_values("idUnico", ascending=True)

    # Create the plot
    fig, ax = plt.subplots(figsize=(14, 8))

    # Create horizontal positions
    y_pos = np.arange(len(butterfly_data))

    # Plot left side (percentages of projects) - negative values
    bars_left = ax.barh(
        y_pos,
        -butterfly_data["projetos_pct"],
        color=palette[0],
        alpha=0.8,
        label="% dos projetos",
    )

    # Plot right side (percentages of investment) - positive values
    bars_right = ax.barh(
        y_pos,
        butterfly_data["investimento_pct"],
        color=palette[1],
        alpha=0.8,
        label="% do investimento",
    )

    # Customize the plot
    ax.set_yticks(y_pos)
    ax.set_yticklabels(butterfly_data["clusterNome"])
    ax.set_xlabel("# Projetos | Investimento")
    ax.set_title(
        "Comparação: Distribuição de Projetos vs Investimento por Grupo",
        fontsize=14,
        fontweight="bold",
        pad=20,
    )

    # Add value labels on bars
    for i, (proj, inv, proj_pct, inv_pct) in enumerate(
        zip(
            butterfly_data["idUnico"],
            butterfly_data["investimentoTotal"],
            butterfly_data["projetos_pct"],
            butterfly_data["investimento_pct"],
        )
    ):
        # Left side labels (projects)
        ax.text(
            -proj_pct - 1,
            i,
            f"{int(proj)}\n({proj_pct:.1f}%)",
            ha="right",
            va="center",
            fontsize=9,
        )

        # Right side labels (investment)
        ax.text(
            inv_pct + 1,
            i,
            f"R$ {inv/1e6:,.0f}M\n({inv_pct:.1f}%)",
            ha="left",
            va="center",
            fontsize=9,
        )

    # Add vertical line at zero
    ax.axvline(x=0, color="black", linewidth=0.8, alpha=0.5)

    # Center the plot by using same scale on both sides
    max_pct = max(
        butterfly_data["projetos_pct"].max(), butterfly_data["investimento_pct"].max()
    )
    ax.set_xlim(-max_pct * 1.3, max_pct * 1.3)

    # Add legend
    ax.legend(loc="upper right", bbox_to_anchor=(1, 1))

    # Remove top and right spines
    ax.spines["top"].set_visible(False)
    ax.spines["right"].set_visible(False)

    plt.tight_layout()
    plt.show()


create_butterfly_plot(df, palette)
No description has been provided for this image

Em resumo:

Grupo # Projetos % Projetos Investimento Percentual
Engenharia civil 224 31.6% R$ 2,116M 25.2%
Educação superior 92 13.0% R$ 339M 4.0%
Educação básica 85 12.0% R$ 38M 0.5%
Assessoria/terceirização 81 11.4% R$ 4,059M 48.3%
Transporte 67 9.5% R$ 492M 5.9%
Outros 60 8.5% R$ 1,253M 14.9%
Obra de quadra escolar 26 3.7% R$ 4M 0.0%
Serviço social 26 3.7% R$ 26M 0.3%
Saúde 24 3.4% R$ 27M 0.3%
Energia 23 3.2% R$ 48M 0.6%

Os recursos são altamente concentrados na áreas de assessoria e construção civil. O total de investimentos dos projetos registrados é 8.4 bilhões de reais. Destes, cerca de 50% foram para projetos de assessoria/terceirização, e 25% para projetos de engenharia civil. Menos de 1% foram direcionados a projetos de serviço social, saúde, educação básica e energia.

Educação superior recebeu 8 vezes mais investimento que a educação básica -- mesmo as duas tendo o mesmo um número similar de projetos.

Todas essas interpretações dependem do efeito de seleção de dados. Dados de diferentes áreas possuem diferentes tendêndias de apresentação de informações. Por exemplo, os registros relacionados das obras de educação superior (258 caracteres) e energia (325) são, em média, muito mais longos (e possivelmente detalhados) que os registros da saúde (37) e educação básica (42) -- mais curtos que os de cobertura de quadra escolar (52).

4. Resultados e conclusão¶

Este é um relatório que analise dados sobre 712 projetos de infraestrutura que receberam investimento público no Distrito Federal.

Os dados mais interessantes são os de investimento -- especialmente quando combinados com as categorias extraídas do material textual via aprendizado de máquina.

Algumas estatísticas descritivas sobre os dados:

In [ ]:
plot_bim_pie(df, palette)
No description has been provided for this image
In [ ]:
make_barh_plot(df, "natureza", "Natureza dos projetos", palette[0], extra_space=1.18)
No description has been provided for this image
In [ ]:
make_barh_plot(df, "situacao", "Situação dos projetos", palette[1], extra_space=1.14)
No description has been provided for this image
In [ ]:
# handle nulls
especie_vis = df.copy()
especie_vis["especie"] = (
    especie_vis["especie"].cat.add_categories("Sem dados").fillna("Sem dados")
)
make_barh_plot(
    especie_vis, "especie", "Espécies de projetos", palette[2], extra_space=1.15
)
No description has been provided for this image
  • BIM: Apenas 3.5% dos projetos notificou usar BIM (Building Information Modeling).
  • Natureza dos projetos: A maioria dos itens (73%) é classificado como obra, e não como projeto de investimento em infraestrutura.
  • Situação dos projetos: 76% projetos estão cadastrados, com 11% em execução, 8% concluídos e 3% inativados, cancelados, paralisados ou inacabados.
  • Espécies de projetos: 44% construção de novos projetos e 54% reforma, ampliação ou recuperação de projetos existentes.
In [ ]:
plot_investment_ranges(df, palette)
No description has been provided for this image
  • Investimento simbólico: 16.8% dos projetos possuem investimento simbólico, isto é, estão registrados com investimentos de menos de um real.
  • Distribuição exponencial: Grandes investimentos são exponencialmente mais raros que os casos de investimento menor. Valores altos não são outliers, mas parte esperada da distribuição. Isso também significa que a média (R$ 13,9 milhões) é pouco informativa sobre os dados, pois é muito influenciada pelos valores extremos. Os dados apresentam, aproximadamente:
    • Valor mínimo de R$ 4,7 mil
    • Valor máximo de R$ 840 milhões
    • 50% dos projetos estão entre R$ 668 mil e R$ 8,4 milhões
  • Duração: A duração dos projetos apresenta uma distribuição similar, com a grande maioria dos projetos possuindo duração curta e alguns duração extremamente longa:
  • Valor mínimo de 0 dias
  • Valor máximo de 13 anos
  • 50% dos projetos têm duração entre 0,8 e 3 anos
In [ ]:
plot_date_distributions(df, palette)
No description has been provided for this image

Projetos anômalos: Testes estatísticos mostram correlação entre duração prevista e investimento total (ρ=0.65, IC95%=[0.60, 0.69], p<0.001). Isso é esperado, pois projetos maiores tendem a durar mais. Com esses dados podemos calcular os projetos mais anômalos: curtos que custam muito ou demorados que custam pouco. Os 10 mais anômalos estão descritos na tabela abaixo:

ID Descrição Duração Investimento
46882.53-02 CONSTRUÇÃO DE UNIDADE DE ATENÇÃO ESPECIALIZADA... 1 dia R$ 2.493.000,00
43724.53-06 Contratação de empresa do ramo de engenharia/a... 3 anos R$ 4.684,45
557.53-69 Projeto Estratégico do SISFRON... 1.6 anos R$ 839.664.954,32
10613.53-07 Prestação de serviços de disponibilização de a... 4.9 anos R$ 47.700,00
5223.53-75 ELABORAÇÃO DE PROJETOS BÁSICO E EXECUTIVO DE P... 2.2 anos R$ 20.305,41
39020.52-43 execução das obras de adequação de capacidade,... 1.7 anos R$ 352.994.609,29
28451.53-40 ADEQUACAO DE TRECHO RODOVIARIO - ENTRONCAMENTO... 2.1 anos R$ 359.130.057,13
In [ ]:
m = create_investment_map(df)
m
Out[ ]:
Make this Notebook Trusted to load map: File -> Trust Notebook

O mapa indica uma concentração de investimentos na região da Asa Sul. Também há presença significativa áreas que com maior densidade populacional (Ceilândia, Gama, Planaltina). Fora dessas regiões, observam-se poucos ou nenhum investimento registrado (como em São Sebastião, por exemplo). No entanto, a ausência de pontos no mapa pode estar relacionada a limitações no processo de obtenção dos dados, em vez de refletir uma preferência real.

Podemos usar as colunas que contêm texto para analisar padrões semânticos nos dados. Aqui, usamos técnicas de aprendizado de máquina (embeddings, redução de dimensionalidade, clustering) para extrair grupos dos dados textuais. Este processo resulta neste gráfico interativo:

In [ ]:
plot_embeddings_interactive(df_nlp_vis, dimension_names, hue="cluster")

Esta visualização nos permite explorar os dados. Interpretando as descrições dos itens dos diferentes grupos, chegamos às seguintes categorias:

Grupo Categoria
0 Cobertura de quadra escolar
1 Educação básica
2 Saúde
3 Energia
4 Educação superior
5 Assessoria/terceirização
6 Engenharia civil
7 Transporte
8 Serviço social
-1 Outros

Cruzando essas categorias com os dados sobre investimentos, temos o seguinte gráfico:

In [ ]:
create_butterfly_plot(df, palette)
No description has been provided for this image

O total de investimentos dos projetos registrados é 8.4 bilhões de reais. A figura acima sugere que os recursos são altamente concentrados na áreas de assessoria e construção civil. Destes, cerca de 50% foram para projetos de assessoria/terceirização, e 25% para projetos de engenharia civil. Menos de 1% foram direcionados a projetos de serviço social, saúde, educação básica e energia.

Mesmo possuindo um número similar de projetos, a educação superior recebeu 8 vezes mais investimento que a educação básica.

A concentração de recursos em projetos de assessoria deve ser melhor investigada. Seria possível dobrar os investimentos em saúde com a realocação de apenas 0.6% dos recursos dedicados a assessoria.

Este estudo possui algumas limitações. Não foram realizadas análises nas datas efetivas, empregos gerados e população afetada devida à ausência de dados (95% de nulos); pelo mesmo motivo, a análise dos dados geográficos é deve ser interpretada com cautela (50% de nulos). A qualidade dos dados poderia ser melhorada acrescentando validação para campos como CEP. É possível que usuários tenham preenchido os campos de forma inadequada porque não compreenderam o formulário, ou porque ele não se adequa bem à realidade dos projetos.